package com.css.fxfzypg.modules.rating.task.dao;

import cn.hutool.core.date.DateUtil;
import com.css.fxfzypg.modules.rating.library.model.vo.ExpertScoreProgressVO;
import com.css.fxfzypg.modules.rating.task.model.entity.KpRatingTask;
import org.apache.commons.lang3.ObjectUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

@Repository
public class KpRatingTaskRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public void insert(KpRatingTask kpRatingTask) {
        String sql = "INSERT INTO kp_rating_task(id, name, kesd_id, expert_group_id, end_date, create_time, create_user, update_time, update_user, del_flag, is_run_area_ratio) values(:id, :name, :kesdId, :expertGroupId, :endDate, :createTime, :createUser, :updateTime, :updateUser, :delFlag, :isRunAreaRatio)";
        namedParameterJdbcTemplate.update(sql, new BeanPropertySqlParameterSource(kpRatingTask));
    }

    public List<KpRatingTask> list(KpRatingTask kpRatingTask) {
        StringBuilder sql = new StringBuilder("select * from kp_rating_task where del_flag = '0' ");
        if (ObjectUtils.isNotEmpty(kpRatingTask.getId())) {
            sql.append(" and id = '").append(kpRatingTask.getId()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTask.getName())) {
            sql.append(" and name like '%").append(kpRatingTask.getName()).append("%' ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTask.getKesdId())) {
            sql.append(" and kesd_id = '").append(kpRatingTask.getKesdId()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTask.getExpertGroupId())) {
            sql.append(" and expert_group_id = '").append(kpRatingTask.getExpertGroupId()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTask.getEndDate())) {
            sql.append(" and end_date = '").append(DateUtil.format(kpRatingTask.getEndDate(), "yyyy-MM-dd")).append("' ");
        }
        sql.append(" order by create_time desc ");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(KpRatingTask.class));
    }

    public KpRatingTask getById(String taskId) {
        try {
            return jdbcTemplate.queryForObject("select * from kp_rating_task where id = '" + taskId + "'", new BeanPropertyRowMapper<>(KpRatingTask.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public void deleteById(String taskId, String uid, Date delTime) {
        jdbcTemplate.update("update kp_rating_task set del_flag = '1', update_user = '" + uid + "', update_time = '" + DateUtil.format(delTime, "yyyy-MM-dd HH:mm:ss") + "' where id = '" + taskId + "'");
    }

    public void updateById(KpRatingTask kpRatingTask) {
        StringBuilder sql = new StringBuilder("update kp_rating_task set ");
        if (ObjectUtils.isNotEmpty(kpRatingTask.getId())) {
            sql.append("  id = '").append(kpRatingTask.getId()).append("', ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTask.getName())) {
            sql.append("  name = '").append(kpRatingTask.getName()).append("', ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTask.getKesdId())) {
            sql.append("  kesd_id = '").append(kpRatingTask.getKesdId()).append("', ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTask.getExpertGroupId())) {
            sql.append("  expert_group_id = '").append(kpRatingTask.getExpertGroupId()).append("', ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTask.getEndDate())) {
            sql.append("  end_date = '").append(DateUtil.format(kpRatingTask.getEndDate(), "yyyy-MM-dd")).append("', ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTask.getUpdateTime())) {
            sql.append("  update_time = '").append(DateUtil.format(kpRatingTask.getUpdateTime(), "yyyy-MM-dd HH:mm:ss")).append("', ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTask.getUpdateUser())) {
            sql.append("  update_user = '").append(kpRatingTask.getUpdateUser()).append("', ");
        }
/*        if (ObjectUtils.isNotEmpty(kpRatingTask.getIsRunAreaRatio())) {
            sql.append("  is_run_area_ratio = ").append(kpRatingTask.getIsRunAreaRatio()).append(", ");
        }*/
        if (ObjectUtils.isNotEmpty(kpRatingTask.getIsRunAreaRatio())) {
            sql.append("  is_run_area_ratio = ").append("1").append(", ");
        }
        sql.deleteCharAt(sql.lastIndexOf(", "));
        sql.append(" where id = '").append(kpRatingTask.getId()).append("' ");
        jdbcTemplate.update(sql.toString());
    }

    public Integer countByKesdId(String kpkId) {
        String sql = "select count(*) from kp_rating_task where kesd_id = ? and del_flag = '0'";
        return jdbcTemplate.queryForObject(sql, Integer.class, kpkId);
    }

    public List<KpRatingTask> selectByStatus(KpRatingTask kpRatingTask, int status) {
        StringBuilder sql = new StringBuilder("select t1.* from kp_rating_task t1 left join kp_rating_task_progress t2 on t1.id = t2.krt_id where t2.status = ? and t1.del_flag = '0'");
        if (ObjectUtils.isNotEmpty(kpRatingTask.getId())) {
            sql.append(" and t1.id = '").append(kpRatingTask.getId()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTask.getName())) {
            sql.append(" and t1.name like '%").append(kpRatingTask.getName()).append("%' ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTask.getKesdId())) {
            sql.append(" and t1.kesd_id = '").append(kpRatingTask.getKesdId()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTask.getExpertGroupId())) {
            sql.append(" and t1.expert_group_id = '").append(kpRatingTask.getExpertGroupId()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTask.getEndDate())) {
            sql.append(" and t1.end_date = '").append(DateUtil.format(kpRatingTask.getEndDate(), "yyyy-MM-dd")).append("' ");
        }
        sql.append(" order by t1.create_time desc ");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(KpRatingTask.class), status);
    }

    public List<KpRatingTask> getUnExpertScoreTaskByEndDate(Date date) {
        String sql = "select DISTINCT t1.* from kp_rating_task t1 left join kp_rating_task_progress t2 on t1.id = t2.krt_id where (select status from kp_rating_task_progress where krt_id = t1.id order by status desc limit 1) not in (0,1,2) and t1.del_flag = '0' and t1.end_date < '2023-10-01';";
        return null;
    }

    public List<ExpertScoreProgressVO> expertScoreProgress(String krtytId) {
        String sql="SELECT kprte.expert_id,status FROM  kp_rating_task_expert_score_progress AS krtesp LEFT JOIN kp_rating_task_expert AS kprte on krtesp.krte_id = kprte.id  where  krtesp.krtyt_id ='"+krtytId+"'";
        return  jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(ExpertScoreProgressVO.class));
    }
}
